-module(erl_sql_compare_diff).
-author("zhuiyi9009@163.com").

-include("common.hrl").

%% API
-export([get_schema_diff/2]).

get_schema_diff(#schema_sync{table_list = SourceTableSchemaList},
                #schema_sync{table_list = DestTableSchemaList}) ->
    DestSchemaMap = maps:from_list([{Table, Schema}
                                    || #table_schema{table = Table} = Schema <- DestTableSchemaList]),
    DiffLines =
        lists:foldl(
            fun(#table_schema{table = Table, sql = Sql} = SourceTable, AccList) ->
                case maps:find(Table, DestSchemaMap) of
                    {ok, #table_schema{sql = Sql}} ->
                        ?DEBUG("table is same ~p~n", [Table]),
                        AccList;
                    {ok, #table_schema{} = DestTable} ->
                        case diff(SourceTable, DestTable) of
                            [] ->
                                ?WARN("sql diff, but found no change ~p~n", [Table]),
%%                                ?DEBUG("~nSql1 ~w~nSql2 ~w~n", [Sql, DestTable#table_schema.sql]),
                                AccList;
                            DiffLines ->
                                DiffString = string:join(DiffLines, ",\n"),
                                AlterSql = io_lib:format("ALTER TABLE `~s`\n~s;\n\n", [Table, DiffString]),
                                ?DEBUG("AlterSql: ~n~s~n", [AlterSql]),
                                [AlterSql|AccList]
                        end;
                    error ->
                        ?WARN("table not exists, create ~p~n", [Table]),
                        %% remove auto increment
                        MP = re_auto_increment_pattern(),
                        Sql2 = re:replace(Sql, MP, " "),
                        [Sql2 ++ "\n\n"|AccList]
                end
            end, [], SourceTableSchemaList),
    lists:reverse(DiffLines).

diff(#table_schema{field_list = SourceFieldList, index_list = SourceIndexList},
     #table_schema{field_list = DestFieldList, index_list = DestIndexList}) ->
    ChangeFieldLines = diff_source_field(SourceFieldList, DestFieldList),
    DropFieldLines = [],
%%    DropFieldLines = diff_dest_field(SourceFieldList, DestFieldList),

    ChangeIndexLines = diff_source_index(SourceIndexList, DestIndexList),
    DropIndexLines = diff_dest_index(SourceIndexList, DestIndexList),

    %% ignore foreign key

    ChangeFieldLines ++ DropFieldLines ++ ChangeIndexLines ++ DropIndexLines.


%%diff_source_field(SourceFieldList, DestFieldList) ->
%%    {_, AlterLines} =
%%        lists:foldl(
%%            fun(#table_field{name = Name, sql = Sql}, {BeforeFieldName, AccLine}) ->
%%                AccLine2 =
%%                    case lists:keyfind(Name, #table_field.name, DestFieldList) of
%%                        #table_field{sql = Sql} ->
%%                            %% exists
%%                            AccLine;
%%                        #table_field{} ->
%%                            %% field change
%%                            Alter = io_lib:format("CHANGE `~s` ~s", [Name, Sql]),
%%                            [Alter|AccLine];
%%                        false ->
%%                            %% new field
%%                            Alter =
%%                                case BeforeFieldName of
%%                                    "" ->
%%                                        io_lib:format("ADD ~s FIRST", [Sql]);
%%                                    _ ->
%%                                        io_lib:format("ADD ~s AFTER ~s", [Sql, BeforeFieldName])
%%                                end,
%%                            [Alter|AccLine]
%%                    end,
%%                {Name, AccLine2}
%%            end, {"", []}, SourceFieldList),
%%    lists:reverse(AlterLines).
diff_source_field(SourceFieldList, DestFieldList) ->
    diff_source_field_1(SourceFieldList, DestFieldList, "", []).

diff_source_field_1([], [], _BeforeFieldName, AlterLines) ->
    lists:reverse(AlterLines);
diff_source_field_1([#table_field{name = Name} = FiledInfo|Rest], [], BeforeFieldName, AlterLines) ->
    Line = diff_add_field(FiledInfo, BeforeFieldName),
    diff_source_field_1(Rest, [], Name, [Line|AlterLines]);
diff_source_field_1([], [#table_field{} = FiledInfo|Rest], BeforeFieldName, AlterLines) ->
    Line = diff_drop_field(FiledInfo),
    diff_source_field_1([], Rest, BeforeFieldName, [Line|AlterLines]);
diff_source_field_1([#table_field{sql = Sql, name = Name}|SourceRest],
                    [#table_field{sql = Sql, name = Name}|DestRest], _BeforeFieldName, AlterLines) ->
    %% same
    diff_source_field_1(SourceRest, DestRest, Name, AlterLines);
diff_source_field_1([#table_field{name = Name} = FiledInfo|SourceRest],
                    [#table_field{name = Name}|DestRest], _BeforeFieldName, AlterLines) ->
    %% same name, diff sql
    Line = diff_change_field(FiledInfo, ""),
    diff_source_field_1(SourceRest, DestRest, Name, [Line|AlterLines]);
diff_source_field_1([#table_field{name = SourceName} = SourceFiledInfo|SourceRest] = SourceAll,
                    [#table_field{name = DestName} = DestFiledInfo|DestRest] = DestAll,
                    BeforeFieldName, AlterLines) ->
    %% diff name
    case lists:keyfind(DestName, #table_field.name, SourceRest) of
        false ->
            %% delete dest field
            Line = diff_drop_field(DestFiledInfo),
            diff_source_field_1(SourceAll, DestRest, BeforeFieldName, [Line|AlterLines]);
        _ ->
            case lists:keyfind(SourceName, #table_field.name, DestRest) of
                false ->
                    %% new field
                    Line = diff_add_field(SourceFiledInfo, BeforeFieldName),
                    diff_source_field_1(SourceRest, DestAll, SourceName, [Line|AlterLines]);
                _ ->
                    %% fields exists
                    Line = diff_change_field(SourceFiledInfo, BeforeFieldName),
                    %% delete field in dest
                    DestAll2 = lists:keydelete(SourceName, #table_field.name, DestAll),
                    diff_source_field_1(SourceRest, DestAll2, SourceName, [Line|AlterLines])
            end
    end.

diff_add_field(#table_field{sql = Sql}, "") ->
    io_lib:format("ADD ~s FIRST", [Sql]);
diff_add_field(#table_field{sql = Sql}, BeforeFieldName) ->
    io_lib:format("ADD ~s AFTER ~s", [Sql, BeforeFieldName]).

diff_drop_field(#table_field{name = Name}) ->
    io_lib:format("DROP `~s`", [Name]).

diff_change_field(#table_field{name = Name, sql = Sql}, "") ->
    io_lib:format("CHANGE `~s` ~s FIRST", [Name, Sql]);
diff_change_field(#table_field{name = Name, sql = Sql}, BeforeFieldName) ->
    io_lib:format("CHANGE `~s` ~s AFTER ~s", [Name, Sql, BeforeFieldName]).

%%diff_dest_field(SourceFieldList, DestFieldList) ->
%%    DropLines =
%%        lists:foldl(
%%            fun(#table_field{name = Name}, AccLine) ->
%%                case lists:keyfind(Name, #table_field.name, SourceFieldList) of
%%                    false ->
%%                        %% new field
%%                        Alter = io_lib:format("DROP `~s`", [Name]),
%%                        [Alter|AccLine];
%%                    #table_field{} ->
%%                        AccLine
%%                end
%%            end, [], DestFieldList),
%%    lists:reverse(DropLines).

diff_source_index(SourceIndexList, DestIndexList) ->
    AlterLines =
        lists:foldl(
            fun(#table_index{name = Name, sql = Sql} = SourceIndex, AccLine) ->
                case lists:keyfind(Name, #table_index.name, DestIndexList) of
                    false ->
                        %% new index
                        [alter_add_index(SourceIndex)|AccLine];
                    #table_index{sql = Sql} ->
                        %% no change
                        AccLine;
                    #table_index{} = DestIndex ->
                        %% change
                        AccLine2 =
                            case alter_drop_index(DestIndex) of
                                "" ->
                                    AccLine;
                                Line ->
                                    [Line|AccLine]
                            end,
                        [alter_add_index(SourceIndex)|AccLine2]
                end
            end, [], SourceIndexList),
    lists:reverse(AlterLines).

diff_dest_index(SourceIndexList, DestIndexList) ->
    DropLines =
        lists:foldl(
            fun(#table_index{name = Name} = SourceIndex, AccLine) ->
                case lists:keyfind(Name, #table_index.name, SourceIndexList) of
                    false ->
                        case alter_drop_index(SourceIndex) of
                            "" ->
                                AccLine;
                            Line ->
                                [Line|AccLine]
                        end;
                    #table_index{} ->
                        AccLine
                end
            end, [], DestIndexList),
    lists:reverse(DropLines).

alter_add_index(#table_index{sql = Sql}) ->
    io_lib:format("ADD ~s", [Sql]).

%% target index
alter_drop_index(#table_index{type = ?INDEX_TYPE_PRIMARY_KEY}) ->
    "DROP PRIMARY KEY";
alter_drop_index(#table_index{name = Name, type = ?INDEX_TYPE_INDEX}) ->
    io_lib:format("DROP INDEX `~s`", [Name]);
alter_drop_index(#table_index{name = Name, type = ?INDEX_TYPE_FOREIGN_KEY}) ->
    io_lib:format("DROP FOREIGN KEY `~s`", [Name]);
alter_drop_index(#table_index{name = Name, type = Type}) ->
    ?WARN("uknown index type Name ~w, Type ~w~n", [Name, Type]),
    "".

re_auto_increment_pattern() ->
    case persistent_term:get({?MODULE, re_auto_increment_pattern}, undefined) of
        undefined ->
            {ok, MP} = re:compile("\sAUTO_INCREMENT=[1-9]\d*\s", [unicode]),
            persistent_term:put({?MODULE, re_auto_increment_pattern}, MP),
            MP;
        MP ->
            MP
    end.